﻿namespace OleDbDAL.Supplier
{
    using DBUtility;
    using IDal.Supplier;
    using Model.Supplier;
    using Model.Systems;
    using System;
    using System.Data;
    using System.Text;
    using WinFormSession;

    public class SupplierApp : ISupplier
    {
        private string dbname = "Systems";

        public void Add(SupplierEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into T_Supplier(");
            builder.Append("ChainNum,SupplierNum,SupplierCode,SupplierName,LinkMan,Tel,Fax,QQNum,Address,OtherTel,ZipCode,Email,IndexPage,CreditRating,CreateMan,TIN,BankName,BankNum,CumulativeMoney,PaidMoney,OutstMoney,UpdateTime,IsVtag,Remark");
            builder.Append(")");
            builder.Append(" values (");
            builder.Append("" + model.ChainNum + ",");
            builder.Append("'" + model.SupplierNum + "',");
            builder.Append("'" + model.SupplierCode + "',");
            builder.Append("'" + model.SupplierName + "',");
            builder.Append("'" + model.LinkMan + "',");
            builder.Append("'" + model.Tel + "',");
            builder.Append("'" + model.Fax + "',");
            builder.Append("'" + model.QQNum + "',");
            builder.Append("'" + model.Address + "',");
            builder.Append("'" + model.OtherTel + "',");
            builder.Append("'" + model.ZipCode + "',");
            builder.Append("'" + model.Email + "',");
            builder.Append("'" + model.IndexPage + "',");
            builder.Append("" + model.CreditRating + ",");
            builder.Append("" + model.CreateMan + ",");
            builder.Append("'" + model.TIN + "',");
            builder.Append("'" + model.BankName + "',");
            builder.Append("'" + model.BankNum + "',");
            builder.Append("" + model.CumulativeMoney + ",");
            builder.Append("" + model.PaidMoney + ",");
            builder.Append("" + model.OutstMoney + ",");
            builder.Append("'" + model.UpdateTime + "',");
            builder.Append("" + model.IsVtag + ",");
            builder.Append("'" + model.Remark + "'");
            builder.Append(")");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public void Delete(int SupplierId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from T_Supplier ");
            builder.Append(" where SupplierId=" + SupplierId);
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public DataTable GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_Supplier ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by SupplierId ");
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public int GetMaxId(string swhere)
        {
            return DbHelperOledb.GetMaxID(this.dbname, "SupplierId", "T_Supplier", swhere);
        }

        public SupplierEnt GetModel(int SupplierId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select SupplierId,ChainNum,SupplierNum,SupplierCode,SupplierName,LinkMan,Tel,Fax,QQNum,Address,OtherTel,ZipCode,Email,IndexPage,CreditRating,CreateMan,TIN,BankName,BankNum,CumulativeMoney,PaidMoney,OutstMoney,UpdateTime,IsVtag,Remark from T_Supplier ");
            builder.Append(" where SupplierId=" + SupplierId);
            SupplierEnt ent = new SupplierEnt();
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            ent.SupplierId = SupplierId;
            if (table.Rows.Count > 0)
            {
                if (table.Rows[0]["ChainNum"].ToString() != "")
                {
                    ent.ChainNum = int.Parse(table.Rows[0]["ChainNum"].ToString());
                }
                ent.SupplierNum = table.Rows[0]["SupplierNum"].ToString();
                ent.SupplierCode = table.Rows[0]["SupplierCode"].ToString();
                ent.SupplierName = table.Rows[0]["SupplierName"].ToString();
                ent.LinkMan = table.Rows[0]["LinkMan"].ToString();
                ent.Tel = table.Rows[0]["Tel"].ToString();
                ent.Fax = table.Rows[0]["Fax"].ToString();
                ent.QQNum = table.Rows[0]["QQNum"].ToString();
                ent.Address = table.Rows[0]["Address"].ToString();
                ent.OtherTel = table.Rows[0]["OtherTel"].ToString();
                ent.ZipCode = table.Rows[0]["ZipCode"].ToString();
                ent.Email = table.Rows[0]["Email"].ToString();
                ent.IndexPage = table.Rows[0]["IndexPage"].ToString();
                if (table.Rows[0]["CreditRating"].ToString() != "")
                {
                    ent.CreditRating = int.Parse(table.Rows[0]["CreditRating"].ToString());
                }
                if (table.Rows[0]["CreateMan"].ToString() != "")
                {
                    ent.CreateMan = int.Parse(table.Rows[0]["CreateMan"].ToString());
                }
                ent.TIN = table.Rows[0]["TIN"].ToString();
                ent.BankName = table.Rows[0]["BankName"].ToString();
                ent.BankNum = table.Rows[0]["BankNum"].ToString();
                if (table.Rows[0]["CumulativeMoney"].ToString() != "")
                {
                    ent.CumulativeMoney = decimal.Parse(table.Rows[0]["CumulativeMoney"].ToString());
                }
                if (table.Rows[0]["PaidMoney"].ToString() != "")
                {
                    ent.PaidMoney = decimal.Parse(table.Rows[0]["PaidMoney"].ToString());
                }
                if (table.Rows[0]["OutstMoney"].ToString() != "")
                {
                    ent.OutstMoney = decimal.Parse(table.Rows[0]["OutstMoney"].ToString());
                }
                if (table.Rows[0]["UpdateTime"].ToString() != "")
                {
                    ent.UpdateTime = DateTime.Parse(table.Rows[0]["UpdateTime"].ToString());
                }
                if (table.Rows[0]["IsVtag"].ToString() != "")
                {
                    ent.IsVtag = int.Parse(table.Rows[0]["IsVtag"].ToString());
                }
                ent.Remark = table.Rows[0]["Remark"].ToString();
                return ent;
            }
            return null;
        }

        public DataTable GetPageList(int PageSize, int PageIndex, string strWhere)
        {
            string orderString = "";
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_Supplier ");
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), strWhere, orderString, out pageCount, out recordCount, "SupplierId");
        }

        public DataTable SearchData(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select SupplierId,SupplierNum,SupplierName,LinkMan,Tel,OutstMoney,UpdateTime,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and T_Procurement.IsInput=1 and T_Procurement.IsClearIngAll=1 and T_Procurement.IsUseReturn=0) as PaidMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and T_Procurement.IsInput=1 and T_Procurement.IsUseReturn=0) as CumulativeMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and year(CreateTime)=year(now()) and month(CreateTime)=Month(now())) as MonthMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and year(CreateTime)=year(now())) as YearMoney");
            builder.Append(" from T_Supplier ");
            if (strWhere != "")
            {
                builder.Append(" where " + strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            builder.Append(" order by SupplierId ");
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            foreach (DataRow row in table.Rows)
            {
                try
                {
                    row["OutstMoney"] = decimal.Parse(row["CumulativeMoney"].ToString()) - decimal.Parse(row["PaidMoney"].ToString());
                }
                catch (Exception)
                {
                }
            }
            return table;
        }

        public DataTable SearchData(int PageSize, int PageIndex, string strWhere)
        {
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select SupplierId,SupplierNum,SupplierName,LinkMan,Tel,OutstMoney,UpdateTime,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and T_Procurement.IsInput=1 and T_Procurement.IsClearIngAll=1 and T_Procurement.IsUseReturn=0) as PaidMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and T_Procurement.IsInput=1 and T_Procurement.IsUseReturn=0) as CumulativeMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and year(CreateTime)=year(now()) and month(CreateTime)=Month(now())) as MonthMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Procurement where T_Procurement.SupplierId=T_Supplier.SupplierId and year(CreateTime)=year(now())) as YearMoney");
            builder.Append(" from T_Supplier ");
            if (strWhere != "")
            {
                builder.Append(" where " + strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            builder.Append(" order by SupplierId desc");
            DataTable table = DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), "", "$%", out pageCount, out recordCount, "SupplierId");
            foreach (DataRow row in table.Rows)
            {
                try
                {
                    row["OutstMoney"] = decimal.Parse(row["CumulativeMoney"].ToString()) - decimal.Parse(row["PaidMoney"].ToString());
                }
                catch (Exception)
                {
                }
            }
            return table;
        }

        public int SearchDataCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select count(*) ");
            builder.Append(" from T_Supplier ");
            if (strWhere != "")
            {
                builder.Append(" where " + strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            if (table.Rows.Count > 0)
            {
                return int.Parse(table.Rows[0][0].ToString());
            }
            return 0;
        }

        public void Update(SupplierEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update T_Supplier set ");
            builder.Append("ChainNum=" + model.ChainNum + ",");
            builder.Append("SupplierNum='" + model.SupplierNum + "',");
            builder.Append("SupplierCode='" + model.SupplierCode + "',");
            builder.Append("SupplierName='" + model.SupplierName + "',");
            builder.Append("LinkMan='" + model.LinkMan + "',");
            builder.Append("Tel='" + model.Tel + "',");
            builder.Append("Fax='" + model.Fax + "',");
            builder.Append("QQNum='" + model.QQNum + "',");
            builder.Append("Address='" + model.Address + "',");
            builder.Append("OtherTel='" + model.OtherTel + "',");
            builder.Append("ZipCode='" + model.ZipCode + "',");
            builder.Append("Email='" + model.Email + "',");
            builder.Append("IndexPage='" + model.IndexPage + "',");
            builder.Append("CreditRating=" + model.CreditRating + ",");
            builder.Append("CreateMan=" + model.CreateMan + ",");
            builder.Append("TIN='" + model.TIN + "',");
            builder.Append("BankName='" + model.BankName + "',");
            builder.Append("BankNum='" + model.BankNum + "',");
            builder.Append("CumulativeMoney=" + model.CumulativeMoney + ",");
            builder.Append("PaidMoney=" + model.PaidMoney + ",");
            builder.Append("OutstMoney=" + model.OutstMoney + ",");
            builder.Append("UpdateTime='" + model.UpdateTime + "',");
            builder.Append("IsVtag=" + model.IsVtag + ",");
            builder.Append("Remark='" + model.Remark + "'");
            builder.Append(" where SupplierId=" + model.SupplierId + "");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }
    }
}

